package com.gukun.springboot.tdengine.service;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.util.RandomUtil;
import com.gukun.springboot.tdengine.pojo.AggregateTagData;
import com.gukun.springboot.tdengine.pojo.TagData;
import com.gukun.springboot.tdengine.pojo.TagInfo;
import com.gukun.springboot.tdengine.domain.enums.FeatureType;
import com.gukun.springboot.tdengine.domain.enums.TagType;
import com.gukun.springboot.tdengine.service.mock.DbsMocktaskService;
import com.taosdata.jdbc.TSDBPreparedStatement;
import com.taosdata.jdbc.ws.TSWSPreparedStatement;
import org.apache.poi.poifs.crypt.dsig.ExpiredCertificateSecurityException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 使用原生JDBC进行数据处理
 * 在插入等功能上，减少sql损耗
 */
@Service()
@Scope("prototype")
public class TDEngineServiceJDBC {

    private static final String TAGTABLE = "tagtable";
    @Value("${tdengine.url:jdbc:TAOS://127.0.0.1:6030?user=root&password=taosdata}")
    private String url = "";

    @Value("${tdengine.db:syncdb}")
    private String dbName = "";


    private static final Logger logger = LoggerFactory.getLogger(TDEngineServiceJDBC.class);

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    /**
     * 连接数据库
     *
     * @return
     * @throws SQLException
     */
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(this.url);
    }

    /**
     * 压缩方式
     *
     * @param dbName
     */
    public void init(String dbName) {
        this.dbName = dbName;
        dropDatabase();
        createDatabase();
        createSupTable();
    }

    public void dropDatabase() {
        String sql = " drop database if exists " + dbName;
        execute(sql);
    }

    public void createDatabase() {
        String sql = " create database if not exists " + dbName + " keep  3650 duration 10 buffer 256 comp 2 wal_level 1 cachemodel 'last_value' ";
        execute(sql);
    }


    public void dropTable(String tbName) {
        final String sql = "drop table if exists " + dbName + "." + tbName + "";
        execute(sql);
    }

    /**
     * 这边建立4个表，存储double型，float型，bool型，int型
     */
    public void createSupTable() {
        for (TagType type : TagType.values()) {
            String sql = " create table if not exists " + getSTableName(type) + "(ts timestamp,val " + type + ", quality tinyint) tags(tagName varchar(128))";
            execute(sql);
        }
    }

    /**
     * 创建表
     *
     * @param tagInfo
     */
    public void createTable(TagInfo tagInfo) {
        String stag = getSTableName(tagInfo.getTagType());
        final String sql = "create table if not exists " + dbName + "." + tagInfo.getTagName() + " using " + stag + " tags('" + tagInfo.getTagName() + "')";
        execute(sql);
    }


    /**
     * 获取超表名称
     *
     * @param type
     * @return
     */
    private String getSTableName(TagType type) {
        String stag = "";
        switch (type) {
            case DOUBLE:
            case BOOL:
            case INT:
            case FLOAT:
                stag = dbName + ".t" + type.name().toLowerCase();
                break;
        }
        return stag;
    }

    /**
     * 批量建表（点）
     *
     * @param tagInfos
     * @throws Exception
     */
    public void createBatchTable(List<TagInfo> tagInfos) throws Exception {
//        if (tagInfos.size() > 5000) {
//            throw new Exception("无法批量插入大于5000个表,请分批插入。");
//        }
        int batchSize = 5000;
        int group = tagInfos.size() / batchSize;
        for (int i = 0; i < group; i++) {
            StringBuilder sql = new StringBuilder("create table ");
            for (int j = 0; j < batchSize; j++) {
                int index = i * batchSize + j;
                TagInfo tagInfo = tagInfos.get(index);
                String tbName = tagInfo.getTagName();
                String sTableName = getSTableName(tagInfo.getTagType());
                sql.append("if not exists ")
                        .append(dbName).append(".")
                        .append(tbName).append(" using ")
                        .append(sTableName).append(" tags('")
                        .append(tagInfo.getTagName()).append("') ");
//                sql += "if not exists " + dbName + "." + tbName + "(ts timestamp,val "+tagInfo.getTagType()+",quality tinyint)";
            }
            execute(sql.toString());
        }

        //查看是否还有余孽
        if (batchSize * group < tagInfos.size()) {
            StringBuilder sql = new StringBuilder("create table ");
            //最后一堆
            for (int i = batchSize * group; i < tagInfos.size(); i++) {
                int index = batchSize * group + i;
                TagInfo tagInfo = tagInfos.get(index);
                String tbName = tagInfo.getTagName();
                String sTableName = getSTableName(tagInfo.getTagType());
                sql.append("if not exists ")
                        .append(dbName).append(".")
                        .append(tbName).append(" using ")
                        .append(sTableName).append(" tags('")
                        .append(tagInfo.getTagName()).append("') ");
//                sql += "if not exists " + dbName + "." + tbName + "(ts timestamp,val "+tagInfo.getTagType()+",quality tinyint)";
            }
            execute(sql.toString());
        }

    }


    /**
     * 存double
     *
     * @param tagDataMap
     * @param ts
     * @throws SQLException
     */
    public void insertBatchDouble(Map<String, Double> tagDataMap, Long ts) throws SQLException {
        excuteSql(tagDataMap, ts);
    }

    /**
     * 存float
     *
     * @param tagDataMap
     * @param ts
     * @throws SQLException
     */
    public void insertBatchFloat(Map<String, Float> tagDataMap, Long ts) throws SQLException {
        excuteSql(tagDataMap, ts);
    }

    /**
     * 存int
     *
     * @param tagDataMap
     * @param ts
     * @throws SQLException
     */
    public void insertBatchInt(Map<String, Integer> tagDataMap, Long ts) throws SQLException {
        excuteSql(tagDataMap, ts);
    }

    /**
     * 存bool
     *
     * @param tagDataMap
     * @param ts
     * @throws SQLException
     */
    public void insertBatchBool(Map<String, Integer> tagDataMap, Long ts) throws SQLException {
        excuteSql(tagDataMap, ts);
    }


    private <T> void excuteSql(Map<String, T> tagDataMap, Long ts) throws SQLException {
        int batch = 30000;
        StringBuilder insertQuery = new StringBuilder("INSERT INTO ");
        List<String> sqls = new ArrayList<>();
        int index = 0;
        for (String tagName : tagDataMap.keySet()) {
            index++;
            insertQuery
                    .append(tagName)
                    .append(" VALUES ")
                    .append("(")
                    .append(ts)
                    .append(", ")
                    .append(tagDataMap.get(tagName))
                    .append(", 0) ");
            if (index != 0 && index % batch == 0) {
                sqls.add(insertQuery.toString());
                insertQuery = new StringBuilder("INSERT INTO ");
            }
        }
        //最后
        if (!insertQuery.toString().equals("INSERT INTO ")) {
            sqls.add(insertQuery.toString());
        }

        System.out.println("全部数据为：" + tagDataMap.size() + ",数据分批为：" + sqls.size());
//        try (var executor = Executors.newVirtualThreadPerTaskExecutor()) {
        // 一种计数方式，类似于 1,000,000
//                executor.execute(() -> {
        try (Connection connection = getConnection(); Statement stmt = connection.createStatement()) {
            stmt.executeUpdate("use " + dbName);
            for (String sql : sqls) {
                stmt.executeUpdate(sql);
            }
            // 执行其他操作
//                        return "Result";
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
//                });
//            }

//        try (Connection connection = getConnection(); Statement stmt = connection.createStatement()) {
//
//            for (String sql : sqls) {
//                stmt.executeUpdate(sql);
//            }
//        }
    }


    /**
     * 批量插入数据
     *
     * @param tagDataMap
     * @param tagType
     * @throws SQLException
     */
    public void insertBatchWithType(Map<String, List<TagData>> tagDataMap
            , TagType tagType) throws SQLException {

//            setValue(tagDataMap, tagType);

        setValueWithSocket(tagDataMap, tagType);

    }

    private void setValueWithSocket(Map<String, List<TagData>> tagDataMap
            , TagType type) throws SQLException {
        try (Connection conn = getConnection()) {
            String psql = "INSERT INTO ? VALUES(?, ?)";
            TSWSPreparedStatement pst = conn.prepareStatement(psql).unwrap(TSWSPreparedStatement.class);
            for (String tagName : tagDataMap.keySet()) {
                if (type == null) {
                    String t = tagDataMap.get(tagName).get(0).getType();
                    type = DbsMocktaskService.getTagType(type, t);
                }
                try {
                    pst.execute("use " + dbName);
                    ArrayList<Long> tss = new ArrayList<>(10000000);
                    ArrayList vals = new ArrayList(10000000);
                    List<TagData> tagDataList = tagDataMap.get(tagName);
                    for (TagData data : tagDataList) {
                        tss.add(data.getTs());

                        switch (type) {
                            case DOUBLE:
                                vals.add(data.getV() + "");
                                break;
                            case BOOL:
                                vals.add(Boolean.parseBoolean(data.getV()) + "");
                                break;
                            case INT:
                                vals.add(Integer.parseInt(data.getV()));
                                break;
                            case FLOAT:
                                vals.add(Float.parseFloat(data.getV()));
                                break;
                        }
                    }
                    pst.setTableName(dbName + "." + tagName);
                    pst.setTimestamp(1, tss);
                    if (type == null) {
                        pst.setFloat(2, vals);
                    } else {
                        switch (type) {
                            case DOUBLE:
                                pst.setDouble(2, vals);
                                break;
                            case BOOL:
                                pst.setBoolean(2, vals);
                                break;
                            case INT:
                                pst.setInt(2, vals);
                                break;
                            case FLOAT:
                                pst.setFloat(2, vals);
                                break;
                        }
                    }
                    long start1 = System.currentTimeMillis();
                    System.out.println(tagName + "开始执行插入......");
                    pst.columnDataAddBatch();
                    pst.columnDataExecuteBatch();
//                     pst.addBatch();
//                     pst.executeBatch();
                    long end1 = System.currentTimeMillis();
                    System.out.println(tagName + "插入数据用时: " + (end1 - start1));
                } catch (Exception e) {

                }
            }
        }
    }


    private void setValue(Map<String, List<TagData>> tagDataMap
            , TagType type) throws SQLException {
        try (Connection conn = getConnection()) {
            String psql = "INSERT INTO ? VALUES(?, ?, ?)";

            for (String tagName : tagDataMap.keySet()) {
//            try (TSWSPreparedStatement pst = conn.prepareStatement(psql).unwrap(TSWSPreparedStatement.class)) {
                try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
                    ArrayList<Long> tss = new ArrayList<>(10000000);

                    ArrayList<Byte> qualities = new ArrayList<>(10000000);
                    List<TagData> tagDataList = tagDataMap.get(tagName);
                    for (TagData data : tagDataList) {
                        tss.add(data.getTs());
                        qualities.add(data.getQ());
                    }
                    ArrayList vals = getDataList(tagDataList, type);
                    pst.setTableName(dbName + "." + tagName);
                    pst.setTimestamp(0, tss);
                    switch (type) {
                        case DOUBLE:
                            pst.setDouble(1, vals);
                            break;
                        case BOOL:
                            pst.setBoolean(1, vals);
                            break;
                        case INT:
                            pst.setInt(1, vals);
                            break;
                        case FLOAT:
                            pst.setFloat(1, vals);
                            break;
                    }

                    pst.setByte(2, qualities);
                    long start1 = System.currentTimeMillis();
                    System.out.println(tagName + "开始执行插入......");
//                pst.addBatch();
//                int [] exeResult = pst.executeBatch();
                    pst.columnDataAddBatch();
                    pst.columnDataExecuteBatch();

                    long end1 = System.currentTimeMillis();
                    System.out.println(tagName + "插入数据用时: " + (end1 - start1));
                }
            }
        }
    }


    /**
     * 查询数据
     *
     * @param tbName
     * @param start
     * @param end
     * @return
     * @throws SQLException
     */
    public List<TagData> getAllHisData(String tbName, DateTime start, DateTime end) {
        String sql = "select *,tagName from " + dbName + "." + tbName + " where ts>='" + start + "' and ts<='" + end + "'";
        List<TagData> tagDataList = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                TagData tagData = new TagData();
                while (resultSet.next()) {
                    Long timestamp = resultSet.getLong(1);
                    String valString = resultSet.getString(2);
                    byte aByte = resultSet.getByte(3);
                    String tagName = resultSet.getString(4);
                    tagData.setTs(timestamp);
                    tagData.setV(valString);
                    tagData.setQ(aByte);
                    tagDataList.add(tagData);
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }

            return tagDataList;
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
        return null;
    }


    /**
     * TDEngine提供分片的数据计算，这边根据采样率进行截取
     * 有4种形式，最大值、最小值、第一个值、最后一个值
     *
     * @param tbName
     * @param start
     * @param end
     * @param step
     * @return
     */
    public List<TagData> getHisDataByStep(String tbName
            , DateTime start
            , DateTime end
            , int step
            , FeatureType model) {
        String valStr = "first(val)";
        switch (model) {
            case MAX:
                valStr = "max(val)";
                break;
            case MIN:
                valStr = "min(val)";
                break;
            case FIRST:
                valStr = "first(val)";
                break;
            case LAST:
                valStr = "last(val)";
                break;
        }
        String sql = "select ts," + valStr + "as val,quality,tagName from "
                + dbName + "." + tbName + " where ts>='" + start + "' and ts<='" + end + "'"
                + " interval(" + step + "s)";
        List<TagData> tagDataList = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                while (resultSet.next()) {
                    TagData tagData = new TagData();
                    Long timestamp = resultSet.getLong(1);
                    String valString = resultSet.getString(2);
                    byte aByte = resultSet.getByte(3);
                    String tagName = resultSet.getString(4);
                    tagData.setTs(timestamp);
                    tagData.setV(valString);
                    tagData.setQ(aByte);
                    tagDataList.add(tagData);
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }

            return tagDataList;
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
        return null;
    }

    /**
     * TDEngine提供统计数据
     * 含：开始时间，结束时间，最大值、最小值、平均值、前值、后值
     *
     * @param tbName
     * @param start
     * @param end
     * @param step
     * @return
     */
    public List<AggregateTagData> getHisAggregateData(String tbName
            , DateTime start
            , DateTime end
            , int step) {
        String sql = "select _wstart as ws,_wend as we," +
                "avg(val) as avg,min(val) as min,max(val) as max," +
                "first(val) as f,last(val) as l from "
                + dbName + "." + tbName + " where ts>='" + start + "' and ts<='" + end + "'"
                + " interval(" + step + "s)";
        List<AggregateTagData> tagDataList = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                while (resultSet.next()) {
                    AggregateTagData tagData = new AggregateTagData();
                    Long wstart = resultSet.getLong("ws");
                    Long wend = resultSet.getLong("we");
                    double avg = resultSet.getDouble("avg");
                    double min = resultSet.getDouble("min");
                    double max = resultSet.getDouble("max");
                    double first = resultSet.getDouble("f");
                    double last = resultSet.getDouble("l");

                    tagData.setStart(wstart);
                    tagData.setEnd(wend);
                    tagData.setAvg(avg);
                    tagData.setFirst(first);
                    tagData.setLast(last);
                    tagData.setMax(max);
                    tagData.setMin(min);
                    tagDataList.add(tagData);
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }

            return tagDataList;
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
        return null;
    }


    /**
     * TDEngine提供分片的数据计算，这边根据采样率进行截取，取第一个点
     *
     * @param tbName
     * @param start
     * @param end
     * @param step
     * @return
     */
    public List<TagData> getFeatureHisDataByStep(String tbName
            , DateTime start
            , DateTime end
            , int step
            , FeatureType featureType) {
        String sql = "select ts,first(val) as val,quality,tagId from "
                + dbName + "." + tbName + " where ts>='" + start + "' and ts<='" + end + "'"
                + " interval(" + step + "s)";
        List<TagData> tagDataList = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                TagData tagData = new TagData();
                while (resultSet.next()) {
                    Long timestamp = resultSet.getLong(1);
                    String valString = resultSet.getString(2);
                    byte aByte = resultSet.getByte(3);
                    String tagName = resultSet.getString(4);
                    tagData.setTs(timestamp);
                    tagData.setV(valString);
                    tagData.setQ(aByte);
                    tagDataList.add(tagData);
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }

            return tagDataList;
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
        return null;
    }


    public int count() {
        int size = getAllTables().size();
        return size;
    }


    /**
     * 获取所有表名（测点名称）
     *
     * @return
     */
    public List<String> getAllTables() {
        String sql = "show " + dbName + ".tables";
        List<String> tables = new ArrayList<>();
        System.out.println(sql);
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                while (resultSet.next()) {
                    tables.add(resultSet.getString(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return tables;
    }

    /**
     * 获取不同类型的数据
     *
     * @param tagDataList
     * @param type
     * @return
     */
    private ArrayList getDataList(List<TagData> tagDataList, TagType type) {
        ArrayList arrayList = new ArrayList(10000000);
        if (type != null) {
            for (TagData tagData : tagDataList) {
                switch (type) {
                    case DOUBLE:
                        arrayList.add(tagData.getV() + "");
                        break;
                    case BOOL:
                        arrayList.add(Boolean.parseBoolean(tagData.getV()) + "");
                        break;
                    case INT:
                        arrayList.add(Integer.parseInt(tagData.getV()));
                        break;
                    case FLOAT:
                        arrayList.add(Float.parseFloat(tagData.getV()));
                        break;
                }
            }
        } else {
            for (TagData tagData : tagDataList) {
                arrayList.add(RandomUtil.getRandom().nextFloat() * 100);
            }
        }
        return arrayList;
    }

    private void printSql(String sql, boolean succeed, long cost) {
        System.out.println("[ " + (succeed ? "OK" : "ERROR!") + " ] time cost: " + cost + " ms, execute statement ====> " + sql);
    }


    private void execute(String sql) {
        long start = System.currentTimeMillis();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                boolean execute = statement.execute(sql);
                long end = System.currentTimeMillis();
                printSql(sql, true, (end - start));
            } catch (SQLException e) {
                long end = System.currentTimeMillis();
                printSql(sql, false, (end - start));
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void printHelp() {
        System.out.println("Usage: java -jar JDBCDemo.jar -host <hostname>");
        System.exit(0);
    }

    public void useDB() {
        execute("USE " + dbName);
    }

    public List<String> showDatabases() {
        String sql = "show databases";
        execute(sql);
        return null;
    }

    /**
     * 将各个表进行分类
     *
     * @param tableNameList
     * @return
     */
    public Map<String, List<String>> getTableInfo() {
        Map<String, List<String>> result = new HashMap<>();
        String sql = "select distinct(tbname) from " + dbName + ".tfloat order by tbname";
        List<String> floatAllTableList = getChildTable(sql);
        sql = "select distinct(tbname) from " + dbName + ".tdouble order by tbname";
        List<String> doubleAllTableList = getChildTable(sql);
        sql = "select distinct(tbname) from " + dbName + ".tint order by tbname";
        List<String> intAllTableList = getChildTable(sql);
        sql = "select distinct(tbname) from " + dbName + ".tbool order by tbname";
        List<String> boolAllTableList = getChildTable(sql);
//            List<String> floatList = new ArrayList<>();
//            List<String> doubleList = new ArrayList<>();
//            List<String> intList = new ArrayList<>();
//            List<String> boolList = new ArrayList<>();
//            for(String tableName : tableNameList){
//                if(floatAllTableList.contains(tableName)){
//                    floatList.add(tableName);
//                }else if(doubleAllTableList.contains(tableName)){
//                    doubleList.add(tableName);
//                }else if(intAllTableList.contains(tableName)){
//                    intList.add(tableName);
//                }else if(boolAllTableList.contains(tableName)){
//                    boolList.add(tableName);
//                }
//            }
        result.put("tfloat", floatAllTableList);
        result.put("tdouble", doubleAllTableList);
        result.put("tint", intAllTableList);
        result.put("tbool", boolAllTableList);

        return result;
    }

    private List<String> getChildTable(String sql) {
        List<String> result = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                while (resultSet.next()) {
                    result.add(resultSet.getString(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result;
    }
}
